package com.ruoyi.common.utils.ps2;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.nio.file.StandardCopyOption;
import java.util.*;
import java.util.stream.Collectors;

public class ExcelImageValidator {

    public static void main(String[] args) {

        String basePath = "J:\\BaiduNetdiskDownload\\新统计\\总图\\只待合并1";
        // 配置路径（修改为实际路径）
        String excelPath = basePath+"\\metadata.xlsx";
        String originalFolderPath =basePath+ "\\原图";
        String editedFolderPath = basePath+ "\\处理图";
        String outputExcelPath =basePath+ "\\metadata-修.xlsx";

        // 创建未使用图片的存放目录
        Path unusedOriginalFolder = Paths.get(originalFolderPath).resolveSibling("unused_original");
        Path unusedEditedFolder = Paths.get(editedFolderPath).resolveSibling("unused_edited");

        try {
            // 1. 准备目录结构
            prepareDirectories(unusedOriginalFolder, unusedEditedFolder);
            System.out.println("未使用图片目录准备完成");

            // 2. 获取文件夹中实际存在的图片名称（区分大小写）
            Map<String, File> originalImageMap = getImageNameFileMap(originalFolderPath);
            Map<String, File> editedImageMap = getImageNameFileMap(editedFolderPath);
            System.out.println("原图数量: " + originalImageMap.size());
            System.out.println("处理图数量: " + editedImageMap.size());

            // 3. 读取Excel数据（包含所有列）
            WorkbookData workbookData = readExcelData(excelPath);
            System.out.println("读取Excel数据完成，共 " + workbookData.rowCount + " 行");

            // 4. 清理无效行并确保A/B列只保留完全匹配的图片名称
            List<RowData> validRows = validateAndCleanRows(workbookData, originalImageMap, editedImageMap);
            System.out.println("数据验证完成，有效行数: " + validRows.size());

            // 5. 生成新的Excel文件（保留所有列）
            createCleanedExcel(validRows, workbookData.headers, outputExcelPath);
            System.out.println("新Excel文件生成完成: " + outputExcelPath);

            // 6. 移动未使用的图片
            moveUnusedImages(originalFolderPath, editedFolderPath,
                    unusedOriginalFolder, unusedEditedFolder, validRows);
            System.out.println("未使用图片迁移完成");

            // 7. 验证最终结果
            validateFinalResults(outputExcelPath, originalFolderPath, editedFolderPath);

        } catch (Exception e) {
            System.err.println("处理过程中发生错误: " + e.getMessage());
            e.printStackTrace();
        }
    }

    private static void prepareDirectories(Path unusedOriginal, Path unusedEdited) throws IOException {
        if (!Files.exists(unusedOriginal)) {
            Files.createDirectories(unusedOriginal);
            System.out.println("创建目录: " + unusedOriginal);
        }
        if (!Files.exists(unusedEdited)) {
            Files.createDirectories(unusedEdited);
            System.out.println("创建目录: " + unusedEdited);
        }
    }

    /**
     * 获取图片名称到文件的映射（区分大小写）
     */
    private static Map<String, File> getImageNameFileMap(String folderPath) {
        File folder = new File(folderPath);
        File[] files = folder.listFiles((dir, name) -> name.toLowerCase().endsWith(".jpeg"));
        Map<String, File> imageMap = new HashMap<>();

        if (files != null) {
            for (File file : files) {
                String fileName = file.getName();
                // 文件名不带扩展名作为键
                String baseName = fileName.substring(0, fileName.lastIndexOf('.'));
                imageMap.put(baseName, file);
            }
        }
        return imageMap;
    }

    private static WorkbookData readExcelData(String excelPath) throws IOException {
        WorkbookData data = new WorkbookData();

        try (FileInputStream fis = new FileInputStream(excelPath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            Sheet sheet = workbook.getSheetAt(0);
            System.out.println("读取工作表: " + sheet.getSheetName());

            // 读取标题行
            Row headerRow = sheet.getRow(0);
            if (headerRow != null) {
                // 修复：检查最后单元格编号，避免负数
                int cellCount = Math.max(0, headerRow.getLastCellNum());
                data.headers = new String[cellCount];

                for (int i = 0; i < cellCount; i++) {
                    Cell cell = headerRow.getCell(i);
                    data.headers[i] = (cell != null) ? getStringValue(cell) : "";
                }
            }

            // 读取数据行
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) continue;

                // 修复：确保最后单元格编号有效
                int cellCount = row.getLastCellNum();
                if (cellCount < 0) cellCount = 0;  // 处理负数情况

                RowData rowData = new RowData();
                rowData.cellValues = new String[cellCount];

                for (int j = 0; j < cellCount; j++) {
                    Cell cell = row.getCell(j);
                    rowData.cellValues[j] = getStringValue(cell);
                }

                // 记录A列和B列的值
                if (rowData.cellValues.length > 0) {
                    rowData.originalName = rowData.cellValues[0];
                }
                if (rowData.cellValues.length > 1) {
                    rowData.editedName = rowData.cellValues[1];
                }

                data.rows.add(rowData);
            }
            data.rowCount = data.rows.size();
        }
        return data;
    }

    private static String getStringValue(Cell cell) {
        if (cell == null) return "";

        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                double numValue = cell.getNumericCellValue();
                if (numValue == Math.floor(numValue)) {
                    return String.valueOf((int) numValue);
                } else {
                    return String.valueOf(numValue);
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";
        }
    }

    private static List<RowData> validateAndCleanRows(WorkbookData workbookData,
                                                      Map<String, File> originalImageMap,
                                                      Map<String, File> editedImageMap) {
        List<RowData> validRows = new ArrayList<>();

        // 记录已使用的图片名称
        Set<String> usedOriginalNames = new HashSet<>();
        Set<String> usedEditedNames = new HashSet<>();

        for (RowData row : workbookData.rows) {
            boolean originalValid = row.originalName != null &&
                    originalImageMap.containsKey(row.originalName);

            boolean editedValid = row.editedName != null &&
                    editedImageMap.containsKey(row.editedName);

            if (originalValid && editedValid) {
                // 检查是否重复使用
                if (usedOriginalNames.contains(row.originalName)) {
                    System.out.println("跳过重复原图: " + row.originalName);
                    continue;
                }
                if (usedEditedNames.contains(row.editedName)) {
                    System.out.println("跳过重复处理图: " + row.editedName);
                    continue;
                }

                // 创建新的行数据
                RowData validRow = new RowData();
                validRow.cellValues = row.cellValues.clone();
                validRow.originalName = row.originalName;
                validRow.editedName = row.editedName;

                validRows.add(validRow);
                usedOriginalNames.add(row.originalName);
                usedEditedNames.add(row.editedName);
            } else {
                System.out.println("删除无效行: Original=" + row.originalName +
                        " (存在: " + originalValid + "), Edited=" + row.editedName +
                        " (存在: " + editedValid + ")");
            }
        }
        return validRows;
    }

    private static void createCleanedExcel(List<RowData> validRows, String[] headers,
                                           String outputPath) throws IOException {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Cleaned Data");

            // 创建标题行
            Row headerRow = sheet.createRow(0);
            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
            }

            // 填充有效数据
            int rowNum = 1;
            for (RowData row : validRows) {
                Row dataRow = sheet.createRow(rowNum++);
                for (int i = 0; i < row.cellValues.length; i++) {
                    Cell cell = dataRow.createCell(i);
                    cell.setCellValue(row.cellValues[i] != null ? row.cellValues[i] : "");
                }
            }

            // 自动调整列宽
            for (int i = 0; i < headers.length; i++) {
                sheet.autoSizeColumn(i);
            }

            // 写入文件
            try (FileOutputStream fos = new FileOutputStream(outputPath)) {
                workbook.write(fos);
            }
        }
    }

    private static void moveUnusedImages(String originalPath, String editedPath,
                                         Path unusedOriginal, Path unusedEdited,
                                         List<RowData> validRows) throws IOException {
        // 收集有效的图片名称（区分大小写）
        Set<String> validOriginals = new HashSet<>();
        Set<String> validEdited = new HashSet<>();

        for (RowData row : validRows) {
            validOriginals.add(row.originalName);
            validEdited.add(row.editedName);
        }

        // 处理原始文件夹
        processFolder(originalPath, unusedOriginal, validOriginals);
        // 处理编辑文件夹
        processFolder(editedPath, unusedEdited, validEdited);
    }

    private static void processFolder(String sourceFolder, Path targetFolder, Set<String> validNames) throws IOException {
        File folder = new File(sourceFolder);
        File[] files = folder.listFiles((dir, name) -> name.toLowerCase().endsWith(".jpg"));

        if (files == null) {
            System.out.println("警告: 目录为空或不存在 - " + sourceFolder);
            return;
        }

        int movedCount = 0;
        for (File file : files) {
            String fileName = file.getName();
            String baseName = fileName.substring(0, fileName.lastIndexOf('.'));

            if (!validNames.contains(baseName)) {
                Path source = file.toPath();
                Path target = targetFolder.resolve(fileName);
                Files.move(source, target, StandardCopyOption.REPLACE_EXISTING);
                movedCount++;
                System.out.println("移动未使用图片: " + fileName + " -> " + target);
            }
        }
        System.out.println("移动完成: " + movedCount + " 个文件从 " + sourceFolder + " 移动到 " + targetFolder);
    }

    /**
     * 验证最终结果：确保Excel行数与文件夹图片数完全一致
     */
    private static void validateFinalResults(String excelPath, String originalPath, String editedPath) throws IOException {
        System.out.println("\n===== 验证最终结果 =====");

        // 1. 获取Excel行数
        int excelRowCount = 0;
        try (FileInputStream fis = new FileInputStream(excelPath);
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(0);
            excelRowCount = sheet.getLastRowNum(); // 标题行不算在内
            System.out.println("Excel行数: " + excelRowCount);
        }

        // 2. 获取原图数量
        Map<String, File> originalImages = getImageNameFileMap(originalPath);
        System.out.println("原图数量: " + originalImages.size());

        // 3. 获取处理图数量
        Map<String, File> editedImages = getImageNameFileMap(editedPath);
        System.out.println("处理图数量: " + editedImages.size());

        // 4. 检查一致性
        if (excelRowCount == originalImages.size() && excelRowCount == editedImages.size()) {
            System.out.println("验证通过: Excel行数与文件夹图片数量完全一致");
        } else {
            System.err.println("验证失败: 数量不一致！");
            System.err.println("Excel行数: " + excelRowCount);
            System.err.println("原图数量: " + originalImages.size());
            System.err.println("处理图数量: " + editedImages.size());
        }
    }

    static class WorkbookData {
        String[] headers;
        List<RowData> rows = new ArrayList<>();
        int rowCount;
    }

    static class RowData {
        String[] cellValues;
        String originalName;
        String editedName;
    }
}